package guda.house.biz.util;

import guda.house.biz.beans.vo.NoticeBillVO;
import guda.house.dal.dataobject.BillElecDO;
import guda.house.dal.dataobject.BillWaterDO;
import guda.house.dal.dataobject.ElecRuleDO;
import guda.house.dal.dataobject.WaterRuleDO;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.util.CellRangeAddress;


import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Created by well on 16/1/8.
 */
public class ExportExcelUtil {

    public static void exportExcel(String title, String[] headers, String[] fields,
                                   Collection dataset, OutputStream out) {
        exportExcel(title, headers, fields,
                dataset, out, "yyyy-MM-dd");
    }

    public static void exportExcel(String title, String[] headers, String[] fields,
                                   Collection dataset, OutputStream out, String pattern) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();


        //产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }

        //遍历集合数据，产生数据行
        Iterator it = dataset.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            Object t = it.next();
            for (int i = 0; i < fields.length; i++) {
                HSSFCell cell = row.createCell(i);
                String fieldName = fields[i];
                String getMethodName = "get"
                        + fieldName.substring(0, 1).toUpperCase()
                        + fieldName.substring(1);
                try {
                    Class tCls = t.getClass();
                    Method getMethod = tCls.getMethod(getMethodName,
                            new Class[]{});
                    Object value = getMethod.invoke(t, new Object[]{});
                    //判断值的类型后进行强制类型转换
                    String textValue = null;
                    if (value instanceof Date) {
                        Date date = (Date) value;
                        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                        textValue = sdf.format(date);
                    } else if (value instanceof byte[]) {
                        // 有图片时，设置行高为60px;
                        row.setHeightInPoints(60);
                        // 设置图片所在列宽度为80px,注意这里单位的一个换算
                        sheet.setColumnWidth(i, (short) (35.7 * 80));
                        // sheet.autoSizeColumn(i);
                        byte[] bsValue = (byte[]) value;
                        HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,
                                1023, 255, (short) 6, index, (short) 6, index);
                        anchor.setAnchorType(2);
                        patriarch.createPicture(anchor, workbook.addPicture(
                                bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
                    } else {
                        //其它数据类型都当作字符串简单处理
                        textValue = value.toString();
                    }
                    //如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
                    if (textValue != null) {
                        Pattern p = Pattern.compile("^//d+(//.//d+)?$");
                        Matcher matcher = p.matcher(textValue);
                        if (matcher.matches()) {
                            //是数字当作double处理
                            cell.setCellValue(Double.parseDouble(textValue));
                        } else {
                            HSSFRichTextString richString = new HSSFRichTextString(textValue);
                            HSSFFont font3 = workbook.createFont();
                            font3.setColor(HSSFColor.BLUE.index);
                            richString.applyFont(font3);
                            cell.setCellValue(richString);
                        }
                    }
                } catch (SecurityException e) {
                    e.printStackTrace();
                } catch (NoSuchMethodException e) {
                    e.printStackTrace();
                } catch (IllegalArgumentException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                } finally {
                    //清理资源
                }
            }

        }
        try {
            workbook.write(out);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }


    public static void exportNoticeBillExcel(String title,
                                             List<NoticeBillVO> noticeBillVOList, OutputStream out) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(title);

        HSSFCellStyle titleStyle =  workbook.createCellStyle();// 设置表头样式
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置居中
        HSSFFont f  = workbook.createFont();
        f.setFontHeightInPoints((short) 14);// 字号
        f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);// 加粗
        titleStyle.setFont(f);
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
        titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中


        int rowNum = 0;
        for (NoticeBillVO noticeBillVO:noticeBillVOList) {
            HSSFRow row = sheet.createRow(rowNum++);
            HSSFCell cellTitle = row.createCell(0);
            cellTitle.setCellStyle(titleStyle);
            sheet.addMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, 7));

            cellTitle.setCellValue("催缴通知单");

            HSSFRow rowSuite = sheet.createRow(rowNum++);
            HSSFCell cellVillage = rowSuite.createCell(0);

            sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum-1, 0, 1));
            cellVillage.setCellValue("社区名称:" + noticeBillVO.getVillageDO().getVillageName());

            HSSFCell cellSuite = rowSuite.createCell(2);
            sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum-1, 2, 3));
            cellSuite.setCellValue("房号:" + noticeBillVO.getSuiteDO().getSuiteName());

            HSSFCell cellOwner = rowSuite.createCell(4);
            sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum-1, 4, 5));
            if(noticeBillVO.getOwnerDOList()!= null && noticeBillVO.getOwnerDOList().size()>0){
                cellOwner.setCellValue("业主:"+noticeBillVO.getOwnerDOList().get(0).getOwnerName());
            }else{
                cellOwner.setCellValue("业主:");
            }

            HSSFCell cellDate = rowSuite.createCell(6);
            sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum-1, 6, 7));
            cellDate.setCellValue("填写日期:"+noticeBillVO.getPrintDay());
            if(noticeBillVO.getBillElecVO()!=null || noticeBillVO.getBillWaterVO()!=null){
                HSSFRow rowWater = sheet.createRow(rowNum++);
                HSSFCell cell = rowWater.createCell(0);
                cell.setCellValue("项目");
                cell = rowWater.createCell(1);
                cell.setCellValue("上月底数");
                cell = rowWater.createCell(2);
                cell.setCellValue("本月抄表数");
                cell = rowWater.createCell(3);
                cell.setCellValue("用电度（水吨）数");
                cell = rowWater.createCell(4);
                cell.setCellValue("单价");
                cell = rowWater.createCell(5);
                cell.setCellValue("本月应付金额");
                cell = rowWater.createCell(6);
                cell.setCellValue("上期欠费 （不含本月数）");
                cell = rowWater.createCell(7);
                cell.setCellValue("累计应交");
                if(noticeBillVO.getBillElecVO()!=null){
                    List<BillElecDO> billElecDOList = noticeBillVO.getBillElecVO().getBillElecDOList();
                    StringBuilder buf = new StringBuilder();
                    for(ElecRuleDO elecRuleDO:noticeBillVO.getBillElecVO().getElecRuleDOList()){
                        if(elecRuleDO.getBuildingTypeId().longValue() == noticeBillVO.getSuiteDO().getBuildingTypeId().longValue()) {
                            buf.append(elecRuleDO.getRuleName()).append(":")
                                    .append(elecRuleDO.getMinValue()).append("-").append(elecRuleDO.getMaxValue())
                                    .append(",费率：")
                                    .append(PriceUtil.formatCNY(elecRuleDO.getFeeUnit())).append("元；");
                        }
                    }
                    for(BillElecDO billElecDO:billElecDOList){
                        rowWater = sheet.createRow(rowNum++);
                        cell = rowWater.createCell(0);
                        cell.setCellValue("电费（" + billElecDO.getRecordYear()+"年"+billElecDO.getRecordMonth()+"月）  ");
                        cell = rowWater.createCell(1);
                        cell.setCellValue(billElecDO.getRecordLast());
                        cell = rowWater.createCell(2);
                        cell.setCellValue(billElecDO.getRecordCurrent());
                        cell = rowWater.createCell(3);
                        cell.setCellValue(billElecDO.getRecordTotal());
                        cell = rowWater.createCell(4);
                        cell.setCellValue(buf.toString());
                        cell = rowWater.createCell(5);
                        cell.setCellValue(PriceUtil.formatCNY(billElecDO.getPayTotal()));
                        cell = rowWater.createCell(6);
                        cell.setCellValue("0");
                        cell = rowWater.createCell(7);
                        cell.setCellValue(PriceUtil.formatCNY(billElecDO.getPayTotal()));

                    }

                }

                if(noticeBillVO.getBillWaterVO()!=null){
                    List<BillWaterDO> billWaterDOList = noticeBillVO.getBillWaterVO().getBillWaterDOList();
                    StringBuilder buf = new StringBuilder();
                    for(WaterRuleDO waterRuleDO:noticeBillVO.getBillWaterVO().getWaterRuleDOList()){
                        buf.append(waterRuleDO.getRuleName()).append(":")
                                .append(waterRuleDO.getMinValue()).append("-").append(waterRuleDO.getMaxValue())
                                .append(",费率：")
                                .append(PriceUtil.formatCNY(waterRuleDO.getFeeUnit())).append("元；");
                    }
                    for(BillWaterDO billWaterDO:billWaterDOList){
                        rowWater = sheet.createRow(rowNum++);
                        cell = rowWater.createCell(0);
                        cell.setCellValue("水费（" + billWaterDO.getRecordYear()+"年"+billWaterDO.getRecordMonth()+"月）  ");
                        cell = rowWater.createCell(1);
                        cell.setCellValue(billWaterDO.getRecordLast());
                        cell = rowWater.createCell(2);
                        cell.setCellValue(billWaterDO.getRecordCurrent());
                        cell = rowWater.createCell(3);
                        cell.setCellValue(billWaterDO.getRecordTotal());
                        cell = rowWater.createCell(4);
                        cell.setCellValue(buf.toString());
                        cell = rowWater.createCell(5);
                        cell.setCellValue(PriceUtil.formatCNY(billWaterDO.getPayTotal()));
                        cell = rowWater.createCell(6);
                        cell.setCellValue("0");
                        cell = rowWater.createCell(7);
                        cell.setCellValue(PriceUtil.formatCNY(billWaterDO.getPayTotal()));

                    }

                }
            }
            if(noticeBillVO.getBillEstateVO()!=null){
                HSSFRow rowTemp = sheet.createRow(rowNum++);
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum , 0, 0));
                HSSFCell cell = rowTemp.createCell(0);
                cell.setCellValue("物业费");
                cell = rowTemp.createCell(1);
                cell.setCellValue("面积");
                cell = rowTemp.createCell(2);
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum-1 , 2, 3));
                cell.setCellValue("应缴时段");
                cell = rowTemp.createCell(4);
                cell.setCellValue("单价");
                cell = rowTemp.createCell(5);
                cell.setCellValue("月物管费");
                cell = rowTemp.createCell(6);
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum -1, 6, 7));
                cell.setCellValue("累计应缴");


                rowTemp = sheet.createRow(rowNum++);
                cell = rowTemp.createCell(1);
                cell.setCellValue(FloatUtil.format2kilogram(noticeBillVO.getSuiteDO().getSuiteArea()));
                cell = rowTemp.createCell(2);
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum-1, 2, 3));
                cell.setCellValue(DateUtil.formatYMD(noticeBillVO.getBillEstateVO().getGmtStart()) + "-" + DateUtil.formatYMD(noticeBillVO.getBillEstateVO().getGmtEnd()));
                cell = rowTemp.createCell(4);
                cell.setCellValue(PriceUtil.formatCNY(noticeBillVO.getBillEstateVO().getEstateRuleDO().getFeeUnit()));
                cell = rowTemp.createCell(5);
                cell.setCellValue(PriceUtil.formatCNY(noticeBillVO.getBillEstateVO().getMonthFee()));
                cell = rowTemp.createCell(6);
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum-1 , 6, 7));
                cell.setCellValue(PriceUtil.formatCNY(noticeBillVO.getBillEstateVO().getTotalFee()));

            }

            if(noticeBillVO.getBillParkingVO()!=null){
                HSSFRow rowTemp = sheet.createRow(rowNum++);
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum , 0, 0));
                HSSFCell cell = rowTemp.createCell(0);
                cell.setCellValue("车位费");
                cell = rowTemp.createCell(1);
                cell.setCellValue("车位号");
                cell = rowTemp.createCell(2);
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum-1 , 2, 3));
                cell.setCellValue("应缴时段");
                cell = rowTemp.createCell(4);
                cell.setCellValue("单价");
                cell = rowTemp.createCell(5);
                cell.setCellValue("月费");
                cell = rowTemp.createCell(6);
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum-1, 6, 7));
                cell.setCellValue("累计应缴");


                rowTemp = sheet.createRow(rowNum++);
                cell = rowTemp.createCell(1);
                cell.setCellValue(noticeBillVO.getBillParkingVO().getParkingDO().getParkingName());
                cell = rowTemp.createCell(2);
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum-1, 2, 3));
                cell.setCellValue(DateUtil.formatYMD(noticeBillVO.getBillParkingVO().getGmtStart()) + "-" + DateUtil.formatYMD(noticeBillVO.getBillParkingVO().getGmtEnd()));
                cell = rowTemp.createCell(4);
                cell.setCellValue(PriceUtil.formatCNY(noticeBillVO.getBillParkingVO().getParkingFee()));
                cell = rowTemp.createCell(5);
                cell.setCellValue(PriceUtil.formatCNY(noticeBillVO.getBillParkingVO().getParkingFee()));
                cell = rowTemp.createCell(6);
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum-1, 6, 7));
                cell.setCellValue(PriceUtil.formatCNY(noticeBillVO.getBillParkingVO().getTotalFee()));

            }
            HSSFRow rowTemp = sheet.createRow(rowNum++);
            HSSFCell cell = rowTemp.createCell(0);
            cell.setCellValue("应缴金额");

            cell = rowTemp.createCell(1);
            sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum-1, 1, 7));
            cell.setCellValue("大写(人民币)：" + noticeBillVO.getAllTotalCN() + " 小写(人民币)：" + PriceUtil.formatCNY(noticeBillVO.getAllTotal()));

            rowTemp = sheet.createRow(rowNum++);
            cell = rowTemp.createCell(0);
            sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum+2, 0, 7));
            cell.setCellValue("1、账号：7401410182600120951 户名：XXX物业管理有限公司 开户行：中信红旗支行\n" +
                    "2、每半年度前5日为本半年度物业费交费日期，过期不交将收取每日3‰的滞纳金。" );


            rowNum = rowNum+5;


        }

        try {
            workbook.write(out);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }
}
